﻿Imports System
Imports System.Data
Imports System.Web.Security
Imports System.Web.Configuration
Imports System.Data.SqlClient
Imports Microsoft.VisualBasic

Public Class UserManagement
    Public Function VerifyUser(ByVal username As String, ByVal password As String) As Boolean
        Dim conn As New DatabaseCommon
        Dim connection As New SqlConnection(conn.ConnectionString)

        Dim command As New SqlCommand("SELECT * FROM [User] WHERE [UserName] = @username AND [UserPassword] = @password", connection)

        command.Parameters.AddWithValue("@username", username)
        command.Parameters.AddWithValue("@password", password)

        command.Connection.Open()
        Dim reader As SqlDataReader = command.ExecuteReader()
        If reader.HasRows Then
            Return True
        Else
            Return False
        End If
        reader.Close()
        command.Connection.Close()
    End Function

    Public Sub CreateUser(ByVal UserName As String, ByVal UserPassword As String, ByVal UserEmail As String)
        Dim db As New DatabaseCommon
        Dim ht As New Hashtable

        ht.Add("@UserName", UserName)
        ht.Add("@UserPassword", UserPassword)
        ht.Add("@UserEmail", UserEmail)

        db.ExecuteProcedure("sp_CreateUser", ht)
    End Sub

    Public Sub PopDDL(ByVal DropDownListQuestion As DropDownList)
        Dim conn As New DatabaseCommon
        Dim connection As New SqlConnection(conn.ConnectionString)
        Dim cmd As New SqlCommand("Select * From [CODES] Where [CategoryID] = 1", connection)

        cmd.Connection.Open()
        Dim ddlValues As SqlDataReader
        ddlValues = cmd.ExecuteReader()

        DropDownListQuestion.DataSource = ddlValues
        DropDownListQuestion.DataValueField = "CodeNameText"
        DropDownListQuestion.DataTextField = "CodeNameText"
        DropDownListQuestion.DataBind()

        ddlValues.Close()
        cmd.Connection.Close()
    End Sub

    Public Sub InsertQuestion(ByVal qTitle As String, ByVal qText As String, ByVal User As String, ByVal QFeedback As String, ByVal aAnswer As String)
        Dim db As New DatabaseCommon
        Dim ht As New Hashtable

        ht.Add("@qTitle", qTitle)
        ht.Add("@qText", qText)
        ht.Add("@qSubmittedBy", User)
        ht.Add("@QFeedback", QFeedback)
        ht.Add("@aAnswer", aAnswer)

        db.ExecuteProcedure("sp_InsertQA", ht)
    End Sub

    Public Function RetrievePwd(ByVal email As String) As String
        Dim conn As New DatabaseCommon
        Dim connection As New SqlConnection(conn.ConnectionString)

        Dim pwd As String
        Dim cmd As New SqlCommand("Select [UserPassword] From [User] Where [UserEmail] = @email", connection)

        cmd.Parameters.AddWithValue("@email", email)

        cmd.Connection.Open()
        Dim reader As SqlDataReader = cmd.ExecuteReader()
        If reader.Read Then
            pwd = reader.Item("UserPassword")
            Return pwd
        Else
            Return String.Empty
        End If
        reader.Close()
        cmd.Connection.Close()

    End Function
End Class
